package at.spengergasse.dao.derby;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import at.spengergasse.dao.PlzDao;
import at.spengergasse.dao.exception.DAOException;
import at.spengergasse.dao.exception.NotFoundException;
import at.spengergasse.dao.util.JDBCHelper;
import at.spengergasse.model.Plz;

public class PlzDAODerbyImpl extends AbstractDAODerbyImpl implements PlzDao {

  private static final String SQL_INSERT =
      "INSERT INTO plz(plz_mod_ts, plz_nr, plz_ort) " +
      "VALUES (?, ?, ?)";

  private static final String SQL_UPDATE =
      "UPDATE plz " +
      "SET plz_mod_ts = ?, " +
      "    plz_ort = ? " +
      "WHERE plz_id = ? " +
      "  AND plz_mod_ts = ? ";

  private static final String SQL_DELETE =
      "DELETE FROM plz " +
      "WHERE plz_nr = ? " +
      "  AND plz_mod_ts = ? ";

  private static final String SQL_SELECT_BY_ID =
      "SELECT plz_id, plz_nr, plz_ort, plz_mod_ts " +
      "FROM plz " +
      "WHERE plz_id = ?";

  private static final String SQL_SELECT_ALL =
      "SELECT plz_id, plz_nr, plz_ort, plz_mod_ts " +
      "FROM plz " +
      "ORDER BY plz_nr";

  private static final String SQL_SELECT_BY_NAME_PART =
      "SELECT plz_id, plz_nr, plz_ort, plz_mod_ts " +
      "FROM plz " +
      "WHERE plz_ort LIKE ? " +
      "ORDER BY plz_nr";
  
  private static final String SQL_SELECT_BY_PLZ =
      "SELECT plz_nr, plz_ort, plz_mod_ts " +
      "FROM plz " +
      "WHERE plz_nr = ?";

  private PreparedStatement insertStmt = null;
  private PreparedStatement updateStmt = null;
  private PreparedStatement deleteStmt = null;
  private PreparedStatement selectByIdStmt = null;
  private PreparedStatement selectAllStmt = null;
  private PreparedStatement selectByNamePartStmt = null;
  private PreparedStatement selectByPlzStmt = null;

  public int save(Connection connection, Plz plz) {
    validateConnection(connection);
    validatePlz(plz);
    return (plz.isNew()) ? insert(connection, plz) : update(connection, plz);
  }

  private int insert(Connection connection, Plz plz) {
    validateConnection(connection);
    validatePlz(plz);

    int recordsAffected = 0;
    Date now = new Date();
    ResultSet generatedKeys = null;

    try {
      if (insertStmt == null) {
        insertStmt = connection.prepareStatement(SQL_INSERT, PreparedStatement.RETURN_GENERATED_KEYS);
      }

      insertStmt.setTimestamp(1, JDBCHelper.toSqlTimestamp(now));
      insertStmt.setInt(2, plz.getPlz());
      insertStmt.setString(3, plz.getOrt());

      recordsAffected = insertStmt.executeUpdate();
      generatedKeys = insertStmt.getGeneratedKeys();

      if (generatedKeys.next()) {
        long currentId = generatedKeys.getLong(1);
        plz.setId(currentId);
        plz.setLastModifiedTS(now);
      }
    }
    catch (SQLException e) {
      throw new DAOException("Plz could not be inserted!", e);
    }
    finally {
      closeResultSet(generatedKeys);
    }
    return recordsAffected;
  }

  private int update(Connection connection, Plz plz) {
    validateConnection(connection);
    validatePlz(plz);

    int recordsAffected = 0;
    Date now = new Date();

    try {
      if (updateStmt == null) {
        updateStmt = connection.prepareStatement(SQL_UPDATE);
      }

      updateStmt.setTimestamp(1, JDBCHelper.toSqlTimestamp(now));
      updateStmt.setString(2, plz.getOrt());
      updateStmt.setLong(3, plz.getId());
      updateStmt.setTimestamp(4, JDBCHelper.toSqlTimestamp(plz.getLastModifiedTS()));

      recordsAffected = updateStmt.executeUpdate();

      plz.setLastModifiedTS(now);
    }
    catch (SQLException e) {
      throw new DAOException("Plz could not be updated!", e);
    }
    return recordsAffected;
  }

  public int delete(Connection connection, Plz plz) {
    validateConnection(connection);
    validatePlz(plz);

    int recordsAffected = 0;

    try {
      if (deleteStmt == null) {
        deleteStmt = connection.prepareStatement(SQL_DELETE);
      }

      deleteStmt.setInt(1, plz.getPlz());
      deleteStmt.setTimestamp(2, JDBCHelper.toSqlTimestamp(plz.getLastModifiedTS()));

      recordsAffected = deleteStmt.executeUpdate();
    }
    catch (SQLException e) {
      throw new DAOException("Plz could not be deleted!", e);
    }
    return recordsAffected;
  }

  public Plz findById(Connection connection, Long id) {
    validateConnection(connection);
    if (id == null) throw new IllegalArgumentException("Id darf nicht null sein!");

    Plz foundPlz = null;
    ResultSet rs = null;
    try {
      if (selectByIdStmt == null) {
        selectByIdStmt = connection.prepareStatement(SQL_SELECT_BY_ID);
      }

      selectByIdStmt.setLong(1, id);
      rs = selectByIdStmt.executeQuery();

      while (rs.next()) {
        foundPlz = new Plz(rs.getLong(1), rs.getInt(2), rs.getString(3));
        foundPlz.setLastModifiedTS(JDBCHelper.toDate(rs.getTimestamp(4)));
      }
    }
    catch (SQLException e) {
      throw new NotFoundException("Id could not be found!", e);
    }
    finally {
      closeResultSet(rs);
    }

    return foundPlz;
  }

  public List<Plz> findAll(Connection connection) {
    validateConnection(connection);

    List<Plz> foundPlzList = new ArrayList<Plz>();
    Plz foundPlz = null;
    ResultSet rs = null;

    try {
      if (selectAllStmt == null) {
        selectAllStmt = connection.prepareStatement(SQL_SELECT_ALL);
      }

      rs = selectAllStmt.executeQuery();

      while (rs.next()) {
        foundPlz = new Plz(rs.getLong(1), rs.getInt(2), rs.getString(3));
        foundPlz.setLastModifiedTS(JDBCHelper.toDate(rs.getTimestamp(4)));
        foundPlzList.add(foundPlz);
      }
    }
    catch (SQLException e) {
      throw new NotFoundException("PlzList could not be read!", e);
    }
    finally {
      closeResultSet(rs);
    }

    return foundPlzList;
  }

  public List<Plz> findByNamePart(Connection connection, String namePart) {
    validateConnection(connection);

    List<Plz> foundPlzList = new ArrayList<Plz>();
    Plz foundPlz = null;
    ResultSet rs = null;

    namePart = (namePart.endsWith("%")) ? namePart : namePart + "%";
    namePart = (namePart.startsWith("%")) ? namePart : "%" + namePart;

    try {
      if (selectByNamePartStmt == null) {
        selectByNamePartStmt = connection.prepareStatement(SQL_SELECT_BY_NAME_PART);
      }

      selectByNamePartStmt.setString(1, namePart);
      rs = selectByNamePartStmt.executeQuery();

      while (rs.next()) {
        foundPlz = new Plz(rs.getLong(1), rs.getInt(2), rs.getString(3));
        foundPlz.setLastModifiedTS(JDBCHelper.toDate(rs.getTimestamp(4)));
        foundPlzList.add(foundPlz);
      }
    }
    catch (SQLException e) {
      throw new NotFoundException("PlzList could not be read!", e);
    }
    finally {
      closeResultSet(rs);
    }

    return foundPlzList;
  }
  
  public List<Plz> findByPlz(Connection connection, int plz) {
	    validateConnection(connection);

	    List<Plz> foundPlzList = new ArrayList<Plz>();
	    Plz foundPlz = null;
	    ResultSet rs = null;

	    try {
	      if (selectByPlzStmt == null) {
	    	  selectByPlzStmt = connection.prepareStatement(SQL_SELECT_BY_PLZ);
	      }

	      selectByPlzStmt.setInt(1, plz);
	      rs = selectByPlzStmt.executeQuery();

	      while (rs.next()) {
	        foundPlz = new Plz(rs.getInt(1), rs.getString(2));
	        foundPlz.setLastModifiedTS(JDBCHelper.toDate(rs.getTimestamp(3)));
	        foundPlzList.add(foundPlz);
	      }
	    }
	    catch (SQLException e) {
	      throw new NotFoundException("PlzList could not be read!", e);
	    }
	    finally {
	      closeResultSet(rs);
	    }

	    return foundPlzList;
	  }

  private void validatePlz(Plz plz) {
    if (plz == null) throw new IllegalArgumentException("Zu speichernde Plz darf nicht null sein!");
  }
}